﻿using ExcelHelper.Models;
using Microsoft.VisualBasic.FileIO;
using OfficeOpenXml;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace ExcelHelper
{
    public partial class ExcelHelper
    {
        public static void MakeExcelFileFromTemplate(string template_file, string output_file,object context,string sheet_name="Sheet1")
        {
            ExcelPackage.LicenseContext = LicenseContext.Commercial;

            using (var package_in = new ExcelPackage(new FileInfo(template_file)))
            {

                _LoadTemplateFromFile(package_in, out TemplateParas template_paras, out TemplateContent template_content);


                //如已存在则删除
                if(File.Exists(output_file))
                {
                    File.Delete(output_file);
                }
                using (var package_out = new ExcelPackage(new FileInfo(output_file)))
                {
                    _MakeFileFromTemplate(package_out,context,template_paras,template_content,sheet_name);
                }
            }

            
        }

        static void _LoadTemplateFromFile(ExcelPackage package,out TemplateParas template_paras, out TemplateContent template_content)
        {
            
            template_paras = new TemplateParas();
            template_content = new TemplateContent();
            

            //
            var sheet_paras = package.Workbook.Worksheets["模板参数"]!;
            template_paras.GroupRowStart_fromOne = int.Parse(sheet_paras.Cells[2, 2].Value.ToString());
            template_paras.GroupColStart_fromOne = int.Parse(sheet_paras.Cells[3, 2].Value.ToString());
            template_paras.GroupRowEnd_fromOne = int.Parse(sheet_paras.Cells[4, 2].Value.ToString());
            template_paras.GroupColEnd_fromOne = int.Parse(sheet_paras.Cells[5, 2].Value.ToString());
            template_paras.MultiGroupDirection = sheet_paras.Cells[6, 2].Value.ToString();
            template_paras.MultiGroupStep = int.Parse(sheet_paras.Cells[7, 2].Value.ToString());
            template_paras.MultiItemDirection = sheet_paras.Cells[8, 2].Value.ToString();
            template_paras.MultiItemStep = int.Parse(sheet_paras.Cells[9, 2].Value.ToString());
            template_paras.MultiItemMaxCount = int.Parse(sheet_paras.Cells[10, 2].Value.ToString());

            //
            var sheet_items = package.Workbook.Worksheets["替换表"]!;
            int row = 2;
            while (true)
            {
                //替换文字
                var str_to_replace_val = sheet_items.Cells[row, 1].Value;
                if (str_to_replace_val == null)
                {
                    break;
                }
                var str_to_replace = str_to_replace_val.ToString();
                if (string.IsNullOrWhiteSpace(str_to_replace))
                {
                    break;
                }

                //数据路径（不能为空）
                var data_path = sheet_items.Cells[row, 2].Value.ToString();

                //数据格式
                var data_format = sheet_items.Cells[row, 3].Value?.ToString();

                //
                TemplateReplacingItem item = new TemplateReplacingItem();
                item.StringToReplace = str_to_replace;
                item.PathToData = data_path;
                item.StringFormat = data_format;
                template_paras.ReplacingItems.Add(item);

                //
                row++;
            }

            //
            var sheet_template = package.Workbook.Worksheets["模板"]!;

            template_content.Rows = sheet_template.Rows[template_paras.GroupRowStart_fromOne, template_paras.GroupRowEnd_fromOne];
            template_content.Columns = sheet_template.Columns[template_paras.GroupColStart_fromOne, template_paras.GroupColEnd_fromOne] ;
            template_content.Ranges = sheet_template.Cells[template_paras.GroupRowStart_fromOne, template_paras.GroupColStart_fromOne, template_paras.GroupRowEnd_fromOne, template_paras.GroupColEnd_fromOne];


        }

        static void _MakeFileFromTemplate(ExcelPackage package_out, object context, TemplateParas template_paras, TemplateContent template_content, string sheet_name)
        {
            //获得数据及item_count
            _GetDatas(ref template_paras, context, out int item_count);

            //计算group_count
            int group_count = 1;
            if (template_paras.MultiItemMaxCount > 0)
            {
                group_count = item_count / template_paras.MultiItemMaxCount + (item_count % template_paras.MultiItemMaxCount != 0 ? 1 : 0);
            }

            //
            
            var sheet = package_out.Workbook.Worksheets.Add(sheet_name);

            //
            
            for (int group = 0; group < group_count; group++)
            {

                int row_from = template_paras.GroupRowStart_fromOne;
                int col_from = template_paras.GroupColStart_fromOne;
                int row_to = template_paras.GroupRowEnd_fromOne;
                int col_to = template_paras.GroupColEnd_fromOne;

                var step = template_paras.MultiGroupStep * group;
                if (template_paras.MultiGroupDirection == "向下")
                {
                    row_from += step;
                    row_to += step;
                }
                else
                {
                    col_from += step;
                    col_to += step;
                }

                //内容拷贝
                var range = sheet.Cells[row_from, col_from, row_to, col_to];
                template_content.Ranges.Copy(range);

                //内容修改
                for (int row=row_from;row<=row_to;row++)
                {
                    for(int col=col_from;col<=col_to;col++)
                    {
                        string str = sheet.Cells[row, col].Text;
                        if (!string.IsNullOrWhiteSpace(str))
                        {
                            bool out_of_range;
                            bool multi_item;
                            var str_new = _Replace(str, ref template_paras, group_count, group, out out_of_range, out multi_item);

                            //是否组内跨列
                            if (!multi_item)
                            {
                                //无组内跨列                                
                                sheet.Cells[row, col].Value = str_new;
                            }
                            else
                            {
                                //组内跨列

                                //第一个                             
                                sheet.Cells[row, col].Value = str_new;

                                //组内跨列
                                int limit = template_paras.MultiItemMaxCount < 0 ? int.MaxValue : template_paras.MultiItemMaxCount;
                                for (int i=1;i<limit;i++)
                                {
                                    str_new = _Replace(str, ref template_paras, group_count, group, out out_of_range, out multi_item);
                                    if(out_of_range)
                                    {
                                        break;
                                    }

                                    //拷贝数据
                                    ExcelRange dest = null;
                                    if (template_paras.MultiItemDirection == "向下")
                                    {
                                        dest = sheet.Cells[row + i, col];                                       
                                        
                                    }
                                    else
                                    {
                                        dest = sheet.Cells[row, col + i];
                                    }
                                    dest.Value = str_new;
                                    
                                    //dest.StyleName = sheet.Cells[row, col].StyleName;




                                }
                            }
                            
                            
                        }
                    }
                }

                //行格式拷贝        
                int r = 0;
                foreach (var origin in template_content.Rows)
                {
                    var dest = sheet.Row(row_from + r);
                    dest.Height = origin.Height;
                    //dest.StyleName = origin.StyleName;
                    r++;
                }

                //列格式拷贝
                int c = 0;
                foreach (var origin in template_content.Columns)
                {
                    var dest = sheet.Column(col_from + c);

                    dest.Width = origin.Width;
                    //dest.StyleName = origin.StyleName;
                    c++;
                }

            }

            package_out.Save();

        }

        static List<object> _GetData(object context,string path)
        {
            if (path.StartsWith("/"))
            {
                return null;
            }

            var paths=path.Split('.',StringSplitOptions.RemoveEmptyEntries);
            List<object> objs_out =new List<object> { context};
            foreach(var p in paths)
            {
                List<object> new_vals = new List<object>();
                foreach (var obj in objs_out) {
                    //子元素                    
                    if (p.StartsWith('[') && p.EndsWith("]"))
                    {     
                        var index_str = p.Substring(1, p.Length - 2);
                        int index_val = 0;                        

                        if (index_str == "i")                        {
                            foreach(var o in (obj as IEnumerable)!){
                                new_vals.Add(o);
                            }
                        }
                        else
                        {
                            index_val = int.Parse(index_str);
                            var it = (obj as IEnumerable)!.GetEnumerator();
                            it.Reset();
                            for(int i = 0;i<index_val;i++)
                            {
                                it.MoveNext();
                            }
                            new_vals.Add(it.Current);
                            
                        }


                    }
                    else
                    {
                        var type= obj.GetType();
                        var prop_info=type.GetProperty(p);
                        var val= prop_info!.GetValue(obj, null);
                        if(val != null)
                        {
                            new_vals.Add(val);
                        }
                        
                    }
                }

                objs_out = new_vals;
            }
            return objs_out;
        }

        static void _GetDatas(ref TemplateParas template_paras, object context,out  int item_count)
        {
            item_count = 0;
            foreach(var item in template_paras.ReplacingItems)
            {
                item.Data = _GetData(context, item.PathToData);
                if(item.Data != null )
                {
                    var count = item.Data.Count;
                    if (count > item_count)
                    {
                        item_count = count;
                    }
                }
                
            }
        }

        static string _Replace(string str,ref TemplateParas template_paras,int group_count,int group, out bool out_of_range, out bool multi_item)
        {
            out_of_range = false;
            multi_item = false;
            while(true)
            {
                int p1=str.IndexOf("【#");
                if(p1 == -1)
                {
                    break;
                }
                int p2=str.IndexOf("#】",p1+2);
                if(p2 == -1)
                {
                    break;
                }

                string str_set = "";
                string str_key = str.Substring(p1,p2-p1+2);

                
                var replacing_item = template_paras.ReplacingItems.Where(x => x.StringToReplace == str_key).FirstOrDefault();
                if (replacing_item != null)
                {
                    //
                    var data_list = replacing_item.Data;
                    int data_index = replacing_item.DataIndex++;
                    if(data_list != null)
                    {
                        int data_count = data_list.Count;
                        if (data_count > 0)
                        {
                            if(data_count>1)
                            {
                                multi_item=true;
                            }
                            if (data_index >= data_count)
                            {
                                out_of_range=true;
                                data_index = 0;
                            }                            

                            //
                            var data_val= data_list[data_index];
                            if(data_val != null)
                            {
                                //格式化
                                var format = replacing_item.StringFormat;
                                if (string.IsNullOrWhiteSpace(format))
                                {
                                    str_set=data_val.ToString();
                                }
                                else
                                {
                                    str_set=string.Format(format, data_val);
                                }
                            }

                            
                        }
                        
                    }
                    else
                    {
                        if (replacing_item.PathToData == "/group_count")
                        {
                            str_set=group_count.ToString();
                        }
                        else if (replacing_item.PathToData == "/group_id_from_one")
                        {
                            str_set=(group+1).ToString();
                        }
                    }
                    
                }
                                

                var str_start = str.Substring(0, p1);
                var str_end = str.Substring(p2 + 2, str.Length - p2 - 2);

                str= str_start+str_set + str_end;
            }

            return str;
            
        }
    }
}
